IF EXISTS ( SELECT 'X' FROM sys.procedures WHERE name = 'InsDDLActivity' AND schema_id = 1 )
BEGIN
     IF EXISTS ( SELECT 'X' 
                 FROM   sys.server_triggers 
                 WHERE  parent_class_desc = 'SERVER' 
                 AND    name              = 'LogAllDDL' 
                 AND    is_disabled       = 0 )
     BEGIN
          EXEC( 'DISABLE TRIGGER [LogAllDDL] ON ALL SERVER' )
     END

     DROP PROCEDURE dbo.InsDDLActivity
END
GO

CREATE PROCEDURE dbo.InsDDLActivity
     @EventData xml 
WITH ENCRYPTION 
AS
/*
 *
 * InsDDLActivity
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/29/2009, 1.00.0000
 *
 */
BEGIN
     SET XACT_ABORT ON
     SET NOCOUNT ON
     SET CONCAT_NULL_YIELDS_NULL ON

     INSERT dbo.EventLog
          ( EventLogTypeId,
            LogDt,
            LoginName,
            DatabaseUserName,
            DatabaseName,
            AppName,
            SPID,
            SID,
            LogByApp,
            LogByAppSubSystem,
            LogByDesc,
            LogText,
            TraceText,
            MiscVC1,
            MiscVC2,
            MiscVC3,
            MiscVC4 )
     SELECT 1, -- EventLogTypeId
            @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
            @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),
            @EventData.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)'),
            @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'),
            APP_NAME(),
            @EventData.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),
            SUSER_SID(@EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)')),
            'LogAllDDL', -- LogByApp
            'InsDDLActivity', -- LogByAppSubSystem
            '', -- LogByDesc
            '', -- LogText
            @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
            @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'),
            @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)'),
            @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
            @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)')            
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Inserts a DDL activity row into the EventLog table, this sproc is called from the Server Level ddl trigger.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = InsDDLActivity;
GO

GRANT EXEC ON dbo.InsDDLActivity TO AuditCredUser
GO

-- Enable the trigger
IF EXISTS ( SELECT 'X' 
            FROM   sys.server_triggers 
            WHERE  parent_class_desc = 'SERVER' 
            AND    name              = 'LogAllDDL' 
            AND    is_disabled       = 1 )
BEGIN
     EXEC ( 'ENABLE TRIGGER [LogAllDDL] ON ALL SERVER' )
END
GO
